if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteExistingBudgets]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteExistingBudgets]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/***************************************************************************
* Name:			DeleteExistingBudgets
* Purpose:		This Stored Procedure gets called from Budget feed. This procedure
*				deletes all the budgets created by the feed before feed start re-creating
*				budgets.
*
* SCRAPED DATA		Coded
* -------------		-----
* 
* 
* 
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/
CREATE PROCEDURE [dbo].[DeleteExistingBudgets]
AS
BEGIN

	SET NOCOUNT ON		

	BEGIN TRY

	BEGIN TRANSACTION DeleteExistingBudgets	
	
	/*
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		Delete Table content. Delete rows only that are coming in Agreement view.
		All external budgets should be untouched.
	>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
	*/

		--Get the latest AgreementIDs				
		SELECT
			distinct
			A.Id as AgrAgreementID
		INTO
			#tempExistingAgreement
		FROM
			Stg_IE_Existing_Agreement SEA
			inner join AgrAgreement A on A.PropNumber = SEA.Prop_No
		
		/*
		>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			M a i n  B u d g e t
		>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		*/
		--AgrBudgetItem Table
		SELECT 
			distinct AB.Id as AgrBudgetId 
		INTO
			#tempBudgetItemsICCandidates
		FROM 
			AgrBudget AB
			inner join AgrBudgetContainerView ABCV on ABCV.Id = AB.AgrBudgetContainerViewId
			inner join AgrBudgetContainer ABC on ABC.Id = ABCV.AgrBudgetContainerId
			inner join AgrAgreement A on A.AgrBudgetContainerId = ABC.Id
			inner join #tempExistingAgreement SEA on SEA.AgrAgreementId = A.Id 		
			


		DELETE	
			AgrBudgetItem
		WHERE
			AgrBudgetId in 
				(select AgrBudgetId from #tempBudgetItemsICCandidates) 
				
		--print 'Main Budget Items'
		

		DELETE
			AgrBudgetICRate
		WHERE
			AgrBudgetId in 			
			(select AgrBudgetId from #tempBudgetItemsICCandidates) 
				
		--print 'Main IC Rate'

		--AgrBudget Table		
		DELETE
			AgrBudget
		WHERE
			Id in 
				(select AgrBudgetId from #tempBudgetItemsICCandidates) 
			
		DROP TABLE #tempBudgetItemsICCandidates
		--print 'Main Budget'

		--AgrBudgetContainerView Table
		SELECT 
			distinct ABCV.Id as AgrBudgetContainerViewId
		INTO
			#tempBudgetContainerViewCandidates
		FROM 			
			AgrBudgetContainerView ABCV 
			inner join AgrBudgetContainer ABC on ABC.Id = ABCV.AgrBudgetContainerId
			inner join AgrAgreement A on A.AgrBudgetContainerId = ABC.Id
			inner join #tempExistingAgreement SEA on SEA.AgrAgreementId = A.Id 
			
		DELETE
			AgrBudgetContainerView
		WHERE
			Id in 
				(select AgrBudgetContainerViewId from #tempBudgetContainerViewCandidates) 
			
		DROP TABLE #tempBudgetContainerViewCandidates
		--print 'Main BudgetContainerView'

		/*
		>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
			S u b C o n t r a c t o r  B u d g e t
		>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
		*/
			
		SELECT
			DISTINCT ASM.AgrAgreementId
		INTO
			#tempExistingSubContAgreement
		FROM
			AgrAgreement_SubContractor_Map ASM
			inner join #tempExistingAgreement SEA on SEA.AgrAgreementId = ASM.AgrAgreementId

		--print 'SubcontAgrees'

		SELECT 
			DISTINCT ABC.id as AgrBudgetContainerId
		INTO
			#tempSubContAgrBudgetContainer
		FROM 
			AgrBudgetContainer ABC
			inner join AgrAgreement_SubContractor_Map ASM on ASM.AgrBudgetContainerId = ABC.Id
			inner join #tempExistingSubContAgreement TEA on TEA.AgrAgreementId = ASM.AgrAgreementId 

		--print 'Subcont BudgetContainers'
		--AgrBudgetItem Table
		SELECT 
			AB.Id as AgrBudgetId
		INTO
			#tempSubContBudgetCandidates
		FROM 
			AgrBudget AB
			inner join AgrBudgetContainerView ABCV on ABCV.Id = AB.AgrBudgetContainerViewId
			inner join #tempSubContAgrBudgetContainer ABC on ABC.AgrBudgetContainerId = ABCV.AgrBudgetContainerId													
			

		DELETE	
			AgrBudgetItem
		WHERE
			AgrBudgetId in 
				(select AgrBudgetId from #tempSubContBudgetCandidates) 
		
		--print 'Subcont Budget Items'

		--AgrBudgetICRate Table		
		DELETE
			AgrBudgetICRate
		WHERE
			AgrBudgetId in 			
			(select AgrBudgetId from #tempSubContBudgetCandidates) 
		
		--print 'Subcont ICRates'

		--AgrBudget Table					
		DELETE
			AgrBudget
		WHERE
			Id in 
				(select AgrBudgetId from #tempSubContBudgetCandidates) 
			
		DROP TABLE #tempSubContBudgetCandidates
		--print 'Subcont Budgets'

		--AgrBudgetContainerView Table
		SELECT 
			ABCV.Id as AgrBudgetContainerViewId
		INTO
			#tempSubContBudgetContainerViewCandidates
		FROM 
			AgrBudgetContainerView ABCV 
			inner join #tempSubContAgrBudgetContainer ABC on ABC.AgrBudgetContainerId = ABCV.AgrBudgetContainerId
			
		DELETE
			AgrBudgetContainerView
		WHERE
			Id in 
				(select AgrBudgetContainerViewId from #tempSubContBudgetContainerViewCandidates) 
			
		DROP TABLE #tempSubContBudgetContainerViewCandidates
		--print 'Subcont Budget ContainerView'


		--AgrAgreement_SubContractor_Map Table
		SELECT 
			distinct
			ASM.AgrAgreementId as AgrAgreementId,
			ASM.AgrBudgetContainerId as AgrBudgetContainerId
		INTO
			#tempAgrSubContCandidates
		FROM 
			AgrAgreement A 
			inner join AgrBudgetContainer ABC on ABC.Id = A.AgrBudgetContainerId
			inner join #tempExistingSubContAgreement TEA on TEA.AgrAgreementId = A.Id
			inner join AgrAgreement_SubContractor_Map ASM on ASM.AgrAgreementId = A.Id
														
		DELETE
			AgrAgreement_SubContractor_Map
		WHERE
			Id in 
				(select 
					ASM.Id 
				from 
					AgrAgreement_SubContractor_Map ASM  
					inner join #tempAgrSubContCandidates TASM on TASM.AgrAgreementId = ASM.AgrAgreementId
															and	TASM.AgrBudgetContainerId = ASM.AgrBudgetContainerId) 

		DROP TABLE #tempAgrSubContCandidates	
		--print 'Subcont SubContMap'


		--AgrBudgetContainer
		--It should get deleted from candidate which are from AgrBudgetContainerId
		SELECT 
			ASM.AgrBudgetContainerId as AgrBudgetContainerId
		INTO
			#tempBudgetContainerCandidates
		FROM 
			AgrBudgetContainer ABC 
			inner join #tempSubContAgrBudgetContainer ASM on ASM.AgrBudgetContainerId = ABC.ID


		DELETE
			AgrBudgetContainer
		WHERE
			Id in 
				(select AgrBudgetContainerId from #tempBudgetContainerCandidates) 
			
		--print 'Subcont Budget Container'
		DROP TABLE #tempBudgetContainerCandidates
		DROP TABLE #tempSubContAgrBudgetContainer
		DROP TABLE #tempExistingSubContAgreement
		DROP TABLE #tempExistingAgreement
		
		--RollBack TRANSACTION DeleteExistingBudgets
		COMMIT TRANSACTION DeleteExistingBudgets

	END TRY

    BEGIN CATCH
	DECLARE 
        @ErrorMessage    NVARCHAR(4000),
        @ErrorNumber     INT,
        @ErrorSeverity   INT,
        @ErrorState      INT,
        @ErrorLine       INT,
        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that 
    -- capture information for RAISERROR.
    SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Building the message string that will contain original
    -- error information.
    SELECT @ErrorMessage = 
        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' + 
            'Message: '+ ERROR_MESSAGE();

	IF XACT_STATE() <> 0 Rollback transaction DeleteExistingBudgets

    -- Raise an error: msg_str parameter of RAISERROR will contain
    -- the original error information.
    RAISERROR 
        (
        @ErrorMessage, 
        @ErrorSeverity, 
        1,               
        @ErrorNumber,    -- parameter: original error number.
        @ErrorSeverity,  -- parameter: original error severity.
        @ErrorState,     -- parameter: original error state.
        @ErrorProcedure, -- parameter: original error procedure name.
        @ErrorLine       -- parameter: original error line number.
        );
		return 99
    END CATCH;
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

